import requests
import time
import sqlite3
from lxml import etree
from decimal import Decimal
from datetime import datetime, timedelta
from typing import List, Dict, Any, Optional
from pathlib import Path
from log_config import logger
import csv


# SQLite数据库路径
main_path = Path(__file__).parent
DB_PATH = main_path / "instance" / "data.db"
html_path = "huilv.html"


def get_page():
    """获取网页内容并保存到本地"""
    url = "https://www.boc.cn/sourcedb/whpj/"
    headers = {
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/134.0.0.0 Safari/537.36 Edg/134.0.0.0"
    }
    res = requests.get(url, headers=headers)
    res.encoding = "utf-8"
    with open(html_path, "wb") as f:
        f.write(res.content)

    return res.text


def get_currency_id(currency_name: str) -> Optional[int]:
    """查询currencies表获取货币ID"""
    conn = None
    try:
        conn = sqlite3.connect(DB_PATH)
        cursor = conn.cursor()
        cursor.execute("SELECT id FROM currencies WHERE name_cn = ?", (currency_name,))
        result = cursor.fetchone()
        return result[0] if result else None
    except sqlite3.Error as e:
        print(f"查询货币ID失败: {e}")
        return None
    finally:
        if conn:
            conn.close()


def get_yesterday_rate(currency_id: int, collected_date: str) -> Optional[Decimal]:
    """查询前一天的汇率"""
    current_date = datetime.strptime(collected_date, "%Y-%m-%d").date()
    yesterday = (current_date - timedelta(days=1)).strftime("%Y-%m-%d")

    conn = None
    try:
        conn = sqlite3.connect(DB_PATH)
        cursor = conn.cursor()
        cursor.execute(
            """
            SELECT rate FROM exchange_rates 
            WHERE currency_id = ? AND collected_date = ?
            ORDER BY created_at DESC LIMIT 1
            """,
            (currency_id, yesterday),
        )
        result = cursor.fetchone()
        return Decimal(str(result[0])) if result else None
    except sqlite3.Error as e:
        print(f"查询前一日汇率失败: {e}")
        return None
    finally:
        if conn:
            conn.close()


def parse_exchange_rates(html_content: str) -> List[Dict[str, Any]]:
    """解析HTML并生成数据字典（核心：在此阶段处理汇率类型判断）"""
    result = []
    tree = etree.HTML(html_content)

    # 提取数据行（跳过表头）
    rows = tree.xpath("//tr")[1:]  # 从第2行开始

    for row in rows:
        cells = row.xpath("td")
        if len(cells) < 8:
            continue  # 跳过不完整行

        # 提取基础字段（处理空文本）
        currency_name = (
            cells[0].text.strip() if (cells[0].text and cells[0].text.strip()) else ""
        )
        spot_buy_rate_str = (
            cells[1].text.strip() if (cells[1].text and cells[1].text.strip()) else ""
        )  # 现汇买入价
        cash_buy_rate_str = (
            cells[2].text.strip() if (cells[2].text and cells[2].text.strip()) else ""
        )  # 现钞买入价
        date_time_str = (
            cells[6].text.strip() if (cells[6].text and cells[6].text.strip()) else ""
        )

        # 跳过关键信息为空的行
        if not all([currency_name, date_time_str]):
            print(f"跳过无效行（货币名称或日期为空）: {currency_name}")
            continue

        # 核心逻辑：判断使用现汇买入价还是现钞买入价
        if spot_buy_rate_str:
            # 有现汇买入价，优先使用
            rate_str = spot_buy_rate_str
            data_type = "现汇买入价"
        elif cash_buy_rate_str:
            # 无现汇买入价，使用现钞买入价
            rate_str = cash_buy_rate_str
            data_type = "现钞买入价"
        else:
            # 两者都为空，跳过此行
            print(f"跳过无汇率数据的行: {currency_name}")
            continue

        # 解析日期
        try:
            date_obj = datetime.strptime(date_time_str, "%Y.%m.%d %H:%M:%S")
            collected_date = date_obj.strftime("%Y-%m-%d")
        except ValueError:
            print(f"日期解析失败（跳过）: {date_time_str}")
            continue

        # 获取货币ID
        currency_id = get_currency_id(currency_name)
        if not currency_id:
            print(f"跳过未识别货币: {currency_name}")
            continue

        # 转换汇率为Decimal
        try:
            rate = Decimal(rate_str)
        except ValueError:
            print(f"汇率格式错误（跳过）: {rate_str}")
            continue

        # 计算涨跌幅
        yesterday_rate = get_yesterday_rate(currency_id, collected_date)
        change_amount = Decimal("0.00")
        change_percent = Decimal("0.00")
        if yesterday_rate:
            change_amount = rate - yesterday_rate
            if yesterday_rate != 0:
                change_percent = (change_amount / yesterday_rate) * 100

        # 构建最终字典（此时已明确data_type和rate）
        result.append(
            {
                "currency": currency_name,
                "exchange_rate": {
                    "currency_id": currency_id,
                    "base_currency_code": "CNY",
                    "rate": rate,
                    "change_amount": str(change_amount),
                    "change_percent": str(change_percent),
                    "source": "中国银行",
                    "data_type": data_type,  # 这里已根据实际情况确定类型
                    "collected_date": collected_date,
                },
            }
        )

    return result


def insert_exchange_rate(data: Dict[str, Any]):
    """将字典数据插入数据库（仅负责写入，不处理数据逻辑）"""
    conn = None
    try:
        conn = sqlite3.connect(DB_PATH)
        cursor = conn.cursor()
        ex_rate = data["exchange_rate"]

        # 插入数据（使用IGNORE避免重复）
        cursor.execute(
            """
            INSERT OR IGNORE INTO exchange_rates 
            (currency_id, base_currency_code, rate, change_amount, change_percent, 
             source, data_type, collected_date, created_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, datetime('now', 'localtime'))
            """,
            (
                ex_rate["currency_id"],
                ex_rate["base_currency_code"],
                float(ex_rate["rate"]),
                float(ex_rate["change_amount"]) if ex_rate["change_amount"] else None,
                float(ex_rate["change_percent"]) if ex_rate["change_percent"] else None,
                ex_rate["source"],
                ex_rate["data_type"],  # 直接使用字典中已确定的类型
                ex_rate["collected_date"],
            ),
        )
        conn.commit()
        print(f"成功写入: {data['currency']} ({ex_rate['data_type']})")
    except sqlite3.Error as e:
        print(f"写入失败 {data['currency']}: {e}")
        if conn:
            conn.rollback()
    finally:
        if conn:
            conn.close()


def export_currency_exchange_to_csv(db_path: str, output_csv: str):
    """
    联立查询currencies和exchange_rates表，导出数据到CSV

    :param db_path: SQLite数据库路径
    :param output_csv: 输出的CSV文件路径（如"currency_rates.csv"）
    """
    # 连接数据库
    conn = None
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()

        # 联立查询SQL：关联货币表和汇率表，取每个货币的最新汇率
        query = """
        SELECT 
            c.id AS currency_id,
            c.code AS currency_code,
            c.symbol AS currency_symbol,
            c.name_cn AS currency_name_cn,
            c.name_en AS currency_name_en,
            c.is_active,
            er.rate AS latest_rate,
            er.data_type AS rate_type,
            er.base_currency_code,
            er.change_amount,
            er.change_percent,
            er.source,
            er.collected_date,
            er.created_at
        FROM 
            currencies c
        LEFT JOIN (
            -- 子查询：获取每个货币的最新汇率（按采集日期和创建时间排序）
            SELECT 
                currency_id, 
                rate, 
                data_type, 
                base_currency_code,
                change_amount,
                change_percent,
                source,
                collected_date,
                created_at,
                -- 按货币分组，标记最新的一条记录
                ROW_NUMBER() OVER (
                    PARTITION BY currency_id 
                    ORDER BY collected_date DESC, created_at DESC
                ) AS rn
            FROM exchange_rates
        ) er ON c.id = er.currency_id AND er.rn = 1  -- 只取最新的一条汇率
        ORDER BY 
            c.name_cn ASC;
        """

        # 执行查询
        cursor.execute(query)
        rows = cursor.fetchall()
        if not rows:
            print("未查询到数据")
            return

        # 获取列名（用于CSV表头）
        column_names = [desc[0] for desc in cursor.description]

        # 写入CSV
        with open(output_csv, mode="w", encoding="utf-8-sig", newline="") as f:
            writer = csv.writer(f)
            # 写入表头
            writer.writerow(column_names)
            # 写入数据行
            writer.writerows(rows)

        print(f"成功导出 {len(rows)} 条数据到 {output_csv}")

    except sqlite3.Error as e:
        print(f"查询或导出失败: {e}")
    finally:
        if conn:
            conn.close()


def crawl_exchange_rate():
    html_content = get_page()
    rates_data = parse_exchange_rates(html_content)  # 生成处理后的字典
    for data in rates_data:
        print(data)
        insert_exchange_rate(data)
    logger.info("汇率数据插入完毕")


if __name__ == "__main__":
    # crawl_exchange_rate()
    # print(DB_PATH)
    # 替换为你的数据库路径和输出CSV路径
    OUTPUT_CSV = f"currency_exchange_{datetime.now().strftime('%Y%m%d')}.csv"

    export_currency_exchange_to_csv(DB_PATH, OUTPUT_CSV)
